﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data;
namespace YY.UI
{
    /// <summary> 
    /// ExcelEdit 
    /// </summary> 
    public class ExcelEdit : IDisposable
    {
        public string mFileName;
        private Microsoft.Office.Interop.Excel.Application oAP;
        private Microsoft.Office.Interop.Excel.Workbooks oWBS;
        private Microsoft.Office.Interop.Excel.Workbook oWB;

        public ExcelEdit()
        {
        }

        /// <summary>
        /// Creat Excel
        /// </summary>
        public void Create()
        {
            oAP = new Microsoft.Office.Interop.Excel.Application();
            oWBS = oAP.Workbooks;
            oWB = oWBS.Add(true);
        }

        /// <summary>
        /// Open Excel
        /// </summary>
        /// <param name="fileName"></param>
        public void Open(string fileName)
        {
            oAP = new Microsoft.Office.Interop.Excel.Application();
            oWBS = oAP.Workbooks;
            oWB = oWBS.Open(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            mFileName = fileName;
        }

        /// <summary>
        /// Get Excel Sheet
        /// </summary>
        /// <param name="sheetName">get sheet name</param>
        /// <returns></returns>
        public Microsoft.Office.Interop.Excel.Worksheet GetSheet(string sheetName)
        {
            Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Worksheets[sheetName];
            return workSheet;
        }

        /// <summary>
        /// Addition Excel Sheet
        /// </summary>
        /// <param name="sheetName">append sheet name</param>
        /// <returns></returns>
        public object AddSheet(string sheetName)
        {
            Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            workSheet.Name = sheetName;
            return workSheet;
        }

        /// <summary>
        /// Delete Excel Sheet
        /// </summary>
        /// <param name="sheetName">delete sheet name</param>
        public void DelSheet(string sheetName)
        {
            ((Microsoft.Office.Interop.Excel.Worksheet)oWB.Worksheets[sheetName]).Delete();
        } 

        /// <summary>
        /// Copy Excel Sheet
        /// </summary>
        /// <param name="oldSheetName">OldSheetName - old sheet name</param>
        /// <param name="newSheetName">NewSheetName - copy new sheet</param>
        /// <returns></returns>
        public object RenameSheet(string oldSheetName, string newSheetName)
        {
            Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Worksheets[oldSheetName];
            workSheet.Name = newSheetName;
            return workSheet;
        }
        public object RenameSheet(Microsoft.Office.Interop.Excel.Worksheet sheet, string newSheetName)
        {
            sheet.Name = newSheetName;
            return sheet;
        }

        /// <summary>
        /// Get Cell Value
        /// </summary>
        /// <param name="workSheet">workSheet - get sheet name</param>
        /// <param name="x">x - row</param>
        /// <param name="y">y - loc</param>
        /// <returns></returns>
        public string GetCellValue(Microsoft.Office.Interop.Excel.Worksheet workSheet, int x, int y)
        {
            //return ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[x, y]).Text.ToString();
            return SetNullToEmpty(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[x, y]).Text);
        }

        /// <summary>
        /// Get Cell Value
        /// </summary>
        /// <param name="workSheetName">workSheet - get sheet name</param>
        /// <param name="x">x - row</param>
        /// <param name="y">y - loc</param>
        /// <returns></returns>
        public string GetCellValue(string workSheetName, int x, int y)
        {
            //return ((Microsoft.Office.Interop.Excel.Range)GetSheet(workSheetName).Cells[x, y]).Text.ToString();
            return SetNullToEmpty(((Microsoft.Office.Interop.Excel.Range)GetSheet(workSheetName).Cells[x, y]).Text);
        }


        /// <summary>
        /// Set Cell Value
        /// </summary>
        /// <param name="workSheet">get sheet name</param>
        /// <param name="x">x - row</param>
        /// <param name="y">y - loc</param>
        /// <param name="value">object value</param>
        public void SetCellValue(Microsoft.Office.Interop.Excel.Worksheet workSheet, int x, int y, object value)
        {
            workSheet.Cells[x, y] = value;
        }

        /// <summary>
        /// Set Cell Value
        /// </summary>
        /// <param name="workSheet">get sheet name</param>
        /// <param name="x">x - row</param>
        /// <param name="y">y - loc</param>
        /// <param name="value">object value</param>
        public void SetCellValue(string workSheet, int x, int y, object value)
        {
            GetSheet(workSheet).Cells[x, y] = value;
        }

        /// <summary>
        /// Set Cell Property
        /// </summary>
        /// <param name="workSheet">sheet object</param>
        /// <param name="Startx">row start position</param>
        /// <param name="Starty">col start position</param>
        /// <param name="Endx">row end position</param>
        /// <param name="Endy">col end position</param>
        /// <param name="name">sheet name</param>
        /// <param name="size">font size</param>
        /// <param name="color">font color</param>
        /// <param name="bold">font bold</param>
        /// <param name="hAlignment">Horizontal position</param>
        public void SetCellProperty(Microsoft.Office.Interop.Excel.Worksheet workSheet, int Startx, int Starty, int Endx, int Endy, string name, int size, int color, bool bold, object hAlignment
        )
        {
            workSheet.get_Range(workSheet.Cells[Startx, Starty], workSheet.Cells[Endx, Endy]).Font.Name = name;
            workSheet.get_Range(workSheet.Cells[Startx, Starty], workSheet.Cells[Endx, Endy]).Font.Bold = bold;
            workSheet.get_Range(workSheet.Cells[Startx, Starty], workSheet.Cells[Endx, Endy]).Font.Size = size;
            workSheet.get_Range(workSheet.Cells[Startx, Starty], workSheet.Cells[Endx, Endy]).Font.Color = color;
            workSheet.get_Range(workSheet.Cells[Startx, Starty], workSheet.Cells[Endx, Endy]).HorizontalAlignment = hAlignment;
        }
        public void SetCellProperty(string workSheetName, int Startx, int Starty, int Endx, int Endy, string name, int size, int color, bool bold, object hAlignment
        )
        {
            Microsoft.Office.Interop.Excel.Worksheet workSheet = GetSheet(workSheetName);
            workSheet.get_Range(workSheet.Cells[Startx, Starty], workSheet.Cells[Endx, Endy]).Font.Name = name;
            workSheet.get_Range(workSheet.Cells[Startx, Starty], workSheet.Cells[Endx, Endy]).Font.Bold = bold;
            workSheet.get_Range(workSheet.Cells[Startx, Starty], workSheet.Cells[Endx, Endy]).Font.Size = size;
            workSheet.get_Range(workSheet.Cells[Startx, Starty], workSheet.Cells[Endx, Endy]).Font.Color = color;
            workSheet.get_Range(workSheet.Cells[Startx, Starty], workSheet.Cells[Endx, Endy]).HorizontalAlignment = hAlignment;
        }

        /// <summary>
        /// Sheet Unite Cells
        /// </summary>
        /// <param name="workSheet">sheet object</param>
        /// <param name="x1">row start position</param>
        /// <param name="y1">col start position</param>
        /// <param name="x2">row end position</param>
        /// <param name="y2">col end position</param>
        public void MergeCells(Microsoft.Office.Interop.Excel.Worksheet workSheet, int x1, int y1, int x2, int y2)
        {

            workSheet.get_Range(workSheet.Cells[x1, y1], workSheet.Cells[x2, y2]).Merge(Type.Missing);
        }

        /// <summary>
        /// Sheet Unite Cells
        /// </summary>
        /// <param name="workSheet">sheet object</param>
        /// <param name="x1">row start position</param>
        /// <param name="y1">col start position</param>
        /// <param name="x2">row end position</param>
        /// <param name="y2">col end position</param>
        public void MergeCells(string workSheetName, int x1, int y1, int x2, int y2)
        {
            GetSheet(workSheetName).get_Range(GetSheet(workSheetName).Cells[x1, y1], GetSheet(workSheetName).Cells[x2, y2]).Merge(Type.Missing);
        }

        /// <summary>
        /// more information Insert Sheet
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <param name="workSheetName">sheet name</param>
        /// <param name="startX">row start position</param>
        /// <param name="startY">col start position</param>
        public void InsertTable(DataTable dt, string workSheetName, int startX, int startY)
        {
            for (int i = 0; i <= dt.Rows.Count - 1; i++)
            {
                for (int j = 0; j <= dt.Columns.Count - 1; j++)
                {
                    GetSheet(workSheetName).Cells[startX + i, j + startY] = dt.Rows[i][j].ToString();
                }
            }
        }

        /// <summary>
        /// more information Insert Sheet
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <param name="workSheetName">sheet name</param>
        /// <param name="startX">row start position</param>
        /// <param name="startY">col start position</param>
        public void InsertTable(DataTable dt, Microsoft.Office.Interop.Excel.Worksheet workSheet, int startX, int startY)
        {
            for (int i = 0; i <= dt.Rows.Count - 1; i++)
            {
                for (int j = 0; j <= dt.Columns.Count - 1; j++)
                {
                    workSheet.Cells[startX + i, j + startY] = dt.Rows[i][j].ToString();
                }
            }
        }

        /// <summary>
        /// Save File 
        /// </summary>
        /// <returns></returns>
        public bool Save()
        {
            if (string.IsNullOrEmpty(mFileName))
            {
                return false;
            }
            else
            {
                oWB.Save();
                return true;
            }
        }

        /// <summary>
        /// Save file As
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public bool SaveAs(string fileName)
        {
            if (string.IsNullOrEmpty(fileName))
            {
                return false;
            }
            else
            {
                mFileName = fileName;
                oWB.SaveAs(mFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing);
                return true;
            }
        }

        /// <summary>
        /// 释放
        /// </summary>
        public void Dispose()
        {
            if (oWB != null)
                oWB = null;
            if (oWBS != null)
                oWBS = null;
            if (oAP != null)
            {
                oAP.Quit();
                oAP = null;
            }
            GC.Collect();
        }

        /// <summary>
        /// 关闭后释放
        /// </summary>
        public void Close()
        {
            oWB.Close(true, mFileName, Type.Missing);
            oWBS.Close();
            oAP.Quit();
            oWB = null;
            oWBS = null;
            oAP = null;
            GC.Collect();
        }
        
        /// <summary>
        /// 设为字符窜空
        /// </summary>
        /// <param name="mValue"></param>
        /// <returns></returns>
        private string SetNullToEmpty(Object mValue)
        {
            if (null == mValue || string.Empty.Equals(mValue))
            {
                return string.Empty;
            }
            else
            {
                return mValue.ToString();
            }
        }
    }


}
